from sqlalchemy import Column, Integer, Numeric, String, DateTime, Boolean, Text, DECIMAL, text, create_engine, ForeignKey,ForeignKeyConstraint
from sqlalchemy.orm import sessionmaker
from datetime import datetime
from sqlalchemy.orm import relationship
from sqlalchemy.orm import declarative_base  # 更新导入
import json
import traceback
Base = declarative_base()

class AmazonOrder(Base):
    __tablename__ = 'app_orders_amz'
    id = Column(Integer, primary_key=True, autoincrement=True)
    amazon_order_id = Column(String(255), nullable=False, unique=True)
    seller_order_id = Column(String(255))
    purchase_date = Column(DateTime)
    last_update_date = Column(DateTime)
    order_status = Column(String(50))
    fulfillment_channel = Column(String(50))
    sales_channel = Column(String(100))
    ship_service_level = Column(String(100))
    shipment_service_level_category = Column(String(100))
    number_of_items_shipped = Column(Integer, default=0)
    number_of_items_unshipped = Column(Integer, default=0)
    payment_method = Column(String(50))
    payment_method_details = Column(Text)
    marketplace_id = Column(String(100))
    order_type = Column(String(50))
    earliest_ship_date = Column(DateTime)
    latest_ship_date = Column(DateTime)
    is_business_order = Column(Boolean, default=False)
    is_prime = Column(Boolean, default=False)
    is_premium_order = Column(Boolean, default=False)
    is_global_express_enabled = Column(Boolean, default=False)
    is_replacement_order = Column(Boolean, default=False)
    is_sold_by_ab = Column(Boolean, default=False)
    is_ispu = Column(Boolean, default=False)
    is_access_point_order = Column(Boolean, default=False)
    has_regulated_items = Column(Boolean, default=False)
    buyer_email = Column(String(255))
    currency_code = Column(String(10))
    order_total_amount = Column(DECIMAL(10, 2))
    shipping_address_state = Column(String(100))
    shipping_address_postal_code = Column(String(20))
    shipping_address_city = Column(String(100))
    shipping_address_country_code = Column(String(10))
    created_at = Column(DateTime, server_default=text('CURRENT_TIMESTAMP'))
    updated_at = Column(DateTime, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

# 订单数据处理和导入函数 
class AmazonOrderItem(Base):
    __tablename__ =  'app_order_items_amz'
    id = Column(Integer, primary_key=True, autoincrement=True)
    asin = Column(String(50) )
    seller_sku = Column(String(50) )
    order_item_id = Column(String(50)   )
    title = Column(String(500))
    quantity_ordered = Column(Integer, default=0)
    quantity_shipped = Column(Integer, default=0)
    item_price = Column(DECIMAL(10, 2), default=0.0)
    item_price_currency = Column(String(10), default='')
    item_tax = Column(DECIMAL(10, 2), default=0)
    shipping_price = Column(DECIMAL(10, 2), default=0)
    shipping_tax = Column(DECIMAL(10, 2), default=0)
    promotion_discount = Column(DECIMAL(10, 2), default=0)
    condition = Column(String(50), default='')
    condition_note = Column(Text, default='')
    is_gift = Column(Boolean, default=False)
    gift_message = Column(Text, default='')
    gift_wrap_price = Column(DECIMAL(10, 2), default=0)
    created_at = Column(DateTime )
    updated_at = Column(DateTime )
    order_id = Column(Integer, ForeignKey('app_orders_amz.id'))  # 现在可以使用 ForeignKey
    order = relationship("AmazonOrder", backref="items")     

# 在类定义外部添加外键约束
if hasattr(AmazonOrderItem, '__table__'):
    AmazonOrderItem.__table__.append_constraint(
        ForeignKeyConstraint(['order_id'], ['app_orders_amz.id'])
    )

class commabstract(Base):
    __abstract__ = True 
    id = Column(Integer, primary_key=True, autoincrement=True)
    item_name = Column(String)
    item_description = Column(String)
    listing_id = Column(String)
    seller_sku = Column(String)
    price = Column(String)
    quantity = Column(String)
    open_date = Column(DateTime)
    image_url = Column(String)
    item_is_marketplace = Column(String)
    product_id_type = Column(String)
    zshop_shipping_fee = Column(String)
    item_note = Column(String)
    item_condition = Column(String)
    zshop_category1 = Column(String)
    zshop_browse_path = Column(String)
    zshop_storefront_feature = Column(String)
    asin1 = Column(String)
    asin2 = Column(String)
    asin3 = Column(String)
    will_ship_internationally = Column(String)
    expedited_shipping = Column(String)
    zshop_boldface = Column(String)
    product_id = Column(String)
    bid_for_featured_placement = Column(String)
    add_delete = Column(String)
    pending_quantity = Column(String)
    fulfillment_channel = Column(String)
    merchant_shipping_group = Column(String)
    status = Column(String)
    updated_at = Column(DateTime) 

    bullet_point1 = Column(String)
    bullet_point2 = Column(String)
    bullet_point3 = Column(String)
    bullet_point4 = Column(String)
    bullet_point5 = Column(String)
    bullet_point6 = Column(String)
    bullet_point7 = Column(String)
    bullet_point8 = Column(String)
    bullet_point9 = Column(String)
    bullet_point10 = Column(String)

    brand = Column(String) 
    battery_required = Column(Boolean, default=False)
    item_package_dimensions = Column(String)
    item_package_weight = Column(String)
    number_of_items = Column(Integer)
    material = Column(String)
    unit_count = Column(Integer)
    color = Column(String) 
    item_type_keywords = Column(String)
    
    image2 = Column(String)
    image3 = Column(String)
    image4 = Column(String)
    image5 = Column(String)
    image6 = Column(String)
    image7 = Column(String)
    image8 = Column(String)
    image9 = Column(String)
    image10 = Column(String)
    
    category1 = Column(String)
    category2 = Column(String)
    category3 = Column(String)
    rank1 = Column(Integer)
    rank2 = Column(Integer) 
    rank3 = Column(Integer)

    
    category_p1=Column(String)
    category_p2=Column(String)
    category_p3=Column(String)  


class AmazonProduct(commabstract):
    __tablename__ = 'app_product_amz'

class Amazon_asinupload_xue_todb(Base):
    __tablename__ = 'app_asinsupload_xue'
    trytimes = Column(Integer, default=0)
    id = Column(Integer, primary_key=True, autoincrement=True)
    asin = Column(String)
    opdate = Column(DateTime )
    epid = Column(String, default='')
    processdate=Column(DateTime)
    detail1=Column(String)
    detail2=Column(String)
    detail3=Column(String)
    detail4=Column(String)
    detail5=Column(String)
    image_url=Column(String)
    epid = Column(String)
    brand = Column(String) 
    battery_required = Column(Boolean, default=False)
    item_package_dimensions = Column(String)
    item_package_weight = Column(String)
    number_of_items = Column(Integer)
    material = Column(String)
    unit_count = Column(Integer)
    color = Column(String) 
    item_type_keywords = Column(String)
    
    image2 = Column(String)
    image3 = Column(String)
    image4 = Column(String)
    image5 = Column(String)
    image6 = Column(String)
    image7 = Column(String)
    image8 = Column(String)
    image9 = Column(String)
    image10 = Column(String)
    
    category1 = Column(String)
    category2 = Column(String)
    category3 = Column(String)
    rank1 = Column(Integer)
    rank2 = Column(Integer) 
    rank3 = Column(Integer)

    
    category_p1=Column(String)
    category_p2=Column(String)
    category_p3=Column(String)  
     
    
    

 
class Buyer2(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)  # 主键

    amazon_buyer_id = Column(String)
    display_name = Column(String)
    email = Column(String)

    created_at = Column(DateTime)
    __tablename__ = 'app_buyer'
    def __repr__(self):
        return f"Buyer2(amazon_buyer_id={self.amazon_buyer_id}, display_name={self.display_name}, email={self.email})"

class MessageThread2(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)  # 主键

    buyer = Column(Integer, ForeignKey('app_buyer2.id'))
    thread_id = Column(String)
    last_updated = Column(DateTime)
    is_closed = Column(Boolean)
    __tablename__ = 'app_messagethread'
    def __repr__(self):
        return f"MessageThread2(thread_id={self.thread_id}, last_updated={self.last_updated}, is_closed={self.is_closed})"

import uuid

class Message2(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)  # 主键

    thread = Column(Integer, ForeignKey('app_messagethread2.id'))
    sender = Column(String)
    message_id = Column(String)  # 亚马逊消息ID
    content = Column(Text)
    created_at = Column(DateTime)
    is_read = Column(Boolean)
    
    # 新增字段：用于标识消息唯一性（避免重复同步）
    external_id = Column(String)
    synced_at = Column(DateTime)
    __tablename__ = 'app_message'
    def __repr__(self):
        return f"Message2(message_id={self.message_id}, content={self.content}, created_at={self.created_at}, is_read={self.is_read})"
